import os
import traceback

import pymysql
# from db_entity_stock_ths_gn import ThsGn
from dotenv import load_dotenv

from dbse.mysql.db_entity_stock_ths_exception import StockThsSelException
from utils.DateUtil import getTodayStr


def getDataBase(profile):
    home_path = os.environ.get("PYTHONPATH").split(";")[0]
    print(f'{home_path}\.env.{profile}')
    load_dotenv(f'{home_path}\.env.{profile}')
    # load_dotenv(f'D:\workspace\python\爬虫\stock\.env.{profile}')

    host = os.getenv('DATABASE_HOST')
    port = int(os.getenv('DATABASE_PORT'))
    dbname = os.getenv('DATABASE_NAME')
    user = os.getenv('DATABASE_USER')
    password = os.getenv('DATABASE_PASSWORD')
    db = pymysql.connect(host=host,user=user,password=password,port=port,db=dbname)
    return db

def add(db,sql,gn):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(gn.name,gn.content))
        db.commit()
    except:
        db.rollback()

def addStockNews(db,sql,news):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(news.title,news.content,news.create_time,news.channel,news.news_time,news.url))
        db.commit()
    except:
        db.rollback()

def addThsReportInform(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.stockCode,entity.stockName,entity.informDate,entity.yysr,entity.yysrtbzz,entity.yysrjdhbzz,entity.jlr,entity.jlrtbzz,entity.jlrjdhbzz,entity.mgsy,entity.mgjzc,entity.jzcsyl,entity.mgjyxjll,entity.xsmll))
        db.commit()
    except:
        print("exception occur")
        db.rollback()

def addThsYJYG(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.stockCode,entity.stockName,entity.yjygType,entity.yjygDesc,entity.lrbdfd,entity.sntqjlr,entity.informDate,entity.yjygPeriod))
        db.commit()
    except:
        print("exception occur")
        db.rollback()

def addException(db,stockCode):
    sql = 'INSERT INTO stock_ths_sel_exception (biz_code,sel_type,sta_date) VALUES (%s,%s,%s)'
    entity = StockThsSelException(stockCode,'1',getTodayStr())
    addStockThsSelException(db,sql,entity)

def addThsGN(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.gnCode,entity.gnName,entity.gnUrl,entity.createTime,entity.gnType,entity.gnDesc))
        db.commit()
    except:
        print("exception occur")
        db.rollback()

def addThsGNStocks(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.stockCode,entity.stockName,entity.createTime,entity.gnCode,entity.gnRank,entity.per,entity.pbr,entity.totalCapital,entity.volRatio))
        db.commit()
    except:
        print("详细堆栈信息:")
        traceback.print_exc()  # 打印完整的堆栈跟踪
        print("exception occur")
        db.rollback()

def addStockKpi(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.stock_code,entity.stock_name,entity.price_begin,entity.price_end,entity.price_max,entity.price_min,entity.price_increase,entity.price_increase_rate,entity.per,entity.pbr,entity.sta_date,entity.turnover_ratio,entity.amount,entity.total_capital,entity.trunover,entity.price_yesterday,entity.vol_ratio))
        db.commit()
    except:
        print("exception occur")
        db.rollback()

def addStockThsSelException(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.biz_code,entity.sel_type,entity.sta_date))
        db.commit()
    except:
        print("exception occur")
        db.rollback()

def addStockDfcfInform(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.stock_code,entity.inform_type,entity.inform_title,entity.inform_content,entity.create_date))
        db.commit()
    except:
        print("详细堆栈信息:")
        traceback.print_exc()  # 打印完整的堆栈跟踪
        print("exception occur")
        db.rollback()

def addStockThsGnCurrentInfo(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.gn_code,entity.gn_name,entity.price_begin,entity.price_yesterday,entity.price_end,entity.price_max,entity.price_min,entity.price_increase_rate,entity.rank,entity.up_num,entity.down_num,entity.fund_input,entity.amount,entity.trunover,entity.sta_datetime,entity.price_current))
        db.commit()
    except:
        print("详细堆栈信息:")
        traceback.print_exc()  # 打印完整的堆栈跟踪
        print("exception occur")
        db.rollback()

def addStockThsGnCurrentInfoDaily(db,sql,entity):
    cusor = db.cursor()
    try:
        cusor.execute(sql,(entity.gn_code,entity.gn_name,entity.price_begin,entity.price_yesterday,entity.price_end,entity.price_max,entity.price_min,entity.price_increase_rate,entity.rank,entity.up_num,entity.down_num,entity.fund_input,entity.amount,entity.trunover,entity.sta_datetime,entity.price_current))
        db.commit()
    except:
        print("详细堆栈信息:")
        traceback.print_exc()  # 打印完整的堆栈跟踪
        print("exception occur")
        db.rollback()

def updateGnCompleted(db,gncode):
    sql = 'update stock_scrapy_ths_gn set completed = "1" where gn_code =%s '
    cusor = db.cursor()
    try:
        cusor.execute(sql,(gncode))
        db.commit()
    except:
        print("exception occur")
        db.rollback()

def deleteGnStocks(db,gncode):
    sql = 'delete from stock_scrapy_ths_gn where gn_code =%s '
    cusor = db.cursor()
    try:
        cusor.execute(sql,(gncode))
        db.commit()
    except:
        print("exception occur")
        db.rollback()


def commonDeleteAll(db,tableName):
    sql = f'delete from {tableName} '
    cusor = db.cursor()
    try:
        cusor.execute(sql)
        db.commit()
    except:
        print("exception occur")
        db.rollback()






# db = pymysql.connect(host='39.98.82.109',user='root',password='landleaf@123.com',port=3306,db='stock')
# # sql = "INSERT INTO stock_ths_gn (name, content) VALUES ('雅下概念', '雅鲁藏布江下游相关概念股票')"
# sql = 'INSERT INTO stock_ths_gn (name, content) VALUES (%s, %s)'
# gn = ThsGn('雅下概念','雅鲁藏布江下游相关概念股票')
# add(db,sql,gn)
# db.close()
